﻿using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Configuration;


public class OleSqlite
{
    public string ConnectionString
    {
        get { return ConfigurationManager.AppSettings["oledb_sqlite_connectionString"]; }
    }

    #region public

    public bool TestConnection()
    {
        SQLiteConnection conn = Connection;
        try { conn.Open(); return true; }
        catch { return false; }
    }

    public int ExecuteNonQuery(string cmdTxt, SQLiteParameter[] param)
    {
        using (SQLiteConnection conn = Connection)
        {
            SQLiteCommand cmd = Command;
            ParamFill(conn, cmd, param, cmdTxt);
            int result;
            try { result = cmd.ExecuteNonQuery(); }
            finally { Close(conn); }
            return result;
        }
    }

    public object ExecuteScalar(string cmdTxt, SQLiteParameter[] param)
    {
        using (SQLiteConnection conn = Connection)
        {
            SQLiteCommand cmd = Command;
            ParamFill(conn, cmd, param, cmdTxt);
            object result;
            try { result = cmd.ExecuteScalar(); }
            finally { Close(conn); }
            return result;
        }
    }

    public SQLiteDataReader ExecuteReader(string cmdTxt, SQLiteParameter[] param)
    {
        using (SQLiteConnection conn = Connection)
        {
            SQLiteCommand cmd = Command;
            ParamFill(conn, cmd, param, cmdTxt);
            SQLiteDataReader result;
            try { result = cmd.ExecuteReader(CommandBehavior.CloseConnection); }
            finally { Close(conn); }
            return result;
        }
    }

    public DataTable QueryTable(string cmdTxt, SQLiteParameter[] param)
    {
        using (SQLiteConnection conn = Connection)
        {
            SQLiteCommand cmd = Command;
            var dapter = new SQLiteDataAdapter(cmd);
            var result = new DataTable();
            ParamFill(conn, cmd, param, cmdTxt);
            dapter.Fill(result);
            return result;
        }
    }

    #endregion

    #region private

    private SQLiteConnection Connection
    {
        get { return new SQLiteConnection(ConnectionString); }
    }

    private SQLiteCommand Command
    {
        get { return new SQLiteCommand(); }
    }

    private void ParamFill(SQLiteConnection conn, SQLiteCommand cmd, IEnumerable<SQLiteParameter> param, string cmdTxt)
    {
        if (conn.State == ConnectionState.Closed) conn.Open();
        cmd.Connection = conn;
        cmd.CommandText = cmdTxt;
        if (param == null) return;

        foreach (var item in param) cmd.Parameters.Add(item);
    }

    private void Close(SQLiteConnection conn)
    {
        if (conn.State == ConnectionState.Open) conn.Close();
    }

    #endregion
}
